*-----------------------------------------------------------------
version 11
cap clear
cap log close
set more off

cd "E:\REStat_MS14767_Vol96(2)\Data preparation Compustat segment"
log using "1_cleancompustat.log", replace
*-----------------------------------------------------------------

set mem 700m
set mat 2000

**********************************
* This file is supposed to solve the problem with double entries 
* in the compustat database. Mainly canadian firms are affected
* by this procedure. Which entry is to be kept depends on the information
* the entry may deliver. 
* The resulting file clean_compustat_master contains all
* firms but no doubles.
* The correction of sales is not included.
**************************************

use "raw data/master_compustat_all_countries.dta"

order ticker year CUSIP name sales employees
so CUSIP year

count if CUSIP==CUSIP[_n-1] & year==year[_n-1]
 *5236


*********************************************
* Tickers with a "3" in front mean, that those
* titles are only traded over the counter OR that
* those firms were delisted from NASDAQ. 
* Tickers with a "." in the end mean, that those
* tickers might belong to different firms in Canada
* and US and therefor the Canadian firm gets the "."
* OR that one firm is active and the other one is only 
* available for research.
* Tricky issue!
**************************************************

gen droplater=.
replace droplater=1 if ticker=="ADY." /* ADB SYSTEMS INTL LTD, surviving ticker: 3ADBYF */
replace droplater=1 if ticker=="ATY." /* ATI TECHNOLOGIES INC, surviving ticker: ATYT */
replace droplater=1 if ticker=="ABZ" /* ABER DIAMOND CORP, surviving ticker: ABER */
replace droplater=1 if ticker=="A." /* ABITIBI CONSOLIDATED INC, surviving ticker: ABY */
replace droplater=1 if ticker=="ACE.B" /* ACE AVIATION HOLDINGS INC, surviving ticker: ACEBF */
replace droplater=1 if ticker=="AHX" /* ADHEREX TECHNOLOGIES INC, surviving ticker: ADH */
replace droplater=1 if ticker=="AAV" /* ADVANTAGE ENERGY INCOME FUND, surviving ticker: AVN.U */
replace droplater=1 if ticker=="AEZS" /* AETERNA ZENTARIS INC, surviving ticker: AEZ. */
replace droplater=1 if ticker=="AEM." /* AGNICO EAGLE MINES LTD, surviving ticker: AEM */
replace droplater=1 if ticker=="AGU." /* AGRIUM INC, surviving ticker: AGU */
replace droplater=1 if ticker=="AGT." /* AIMGLOBAL TECHNOLOGIES INC, surviving ticker: AIMG */
replace droplater=1 if ticker=="AL." /* ALCAN INC, surviving ticker: AL */
replace droplater=1 if ticker=="0192B" /* ALDERWOODS GROUP INC, surviving ticker: AWGI */
replace droplater=1 if ticker=="0236B" /* ALGOMA STEEL INC, surviving ticker: AGA */
replace droplater=1 if ticker=="AAC.NV.B" /* ALLIANCE ATLANTIS COMM -CL B, surviving ticker: AACB */
replace droplater=1 if ticker=="AAU" /* ALMADEN MINERALS LTD, surviving ticker: AAM. */
replace droplater=1 if ticker=="3251B" /* ALTAIR NANOTECHNOLOGIES INC, surviving ticker: ALTI */
replace droplater=1 if ticker=="ANPI" /* ANGIOTECH PHARMACEUTICALS, surviving ticker: ANP. */
replace droplater=1 if ticker=="ASW." /* ANGLO SWISS RESOURCES INC, surviving ticker: 3ASWRF */
replace droplater=1 if ticker=="AOM." /* ANORMED INC, surviving ticker: AOM */
replace droplater=1 if ticker=="ARQ" /* ANOORAQ RESOURCES CORP, surviving ticker: ANO */
replace droplater=1 if ticker=="APG" /* APOLLO GOLD CORP, surviving ticker: AGT */
replace droplater=1 if ticker=="APQ." /* ASIA PACIFIC RESOURCES LTD, surviving ticker: APQCF */
replace droplater=1 if ticker=="ASR." /* ASPEN GROUP RESOURCES CORP, surviving ticker: ASRPF */
replace droplater=1 if ticker=="ASV" /* ASPREVA PHARMACEUTICALS CORP, surviving ticker: ASPV */
replace droplater=1 if ticker=="AZK" /* AURIZON MINES LTD, surviving ticker: ARZ. */
replace droplater=1 if ticker=="APX." /* AUSTRAL PACIFIC ENERGY LTD, surviving ticker: AEN */
replace droplater=1 if ticker=="ASM." /* AVINO SILVER&GOLD MINES LTD, surviving ticker: 3ASGMF */
replace droplater=1 if ticker=="AZC." /* AZCO MINING INC/DE, surviving ticker: AZMN */
replace droplater=1 if ticker=="AXP." /* AXCAN PHARMA INC, surviving ticker: AXCA */
replace droplater=1 if ticker=="BCE." /* BCE INC, surviving ticker: BCE */
replace droplater=1 if ticker=="BPR." /* BPI INDUSTRIES INC, surviving ticker: BPG */
replace droplater=1 if ticker=="BLD." /* BALLARD POWER SYSTEMS INC, surviving ticker: BLDP */
replace droplater=1 if ticker=="BAA." /* BANRO CORP, surviving ticker: BAA */
replace droplater=1 if ticker=="ABX." /* BARRICK GOLD CORP, surviving ticker: ABX */
replace droplater=1 if ticker=="BTI." /* BATTERY TECHNOLOGIES INC, surviving ticker: BTIOQ */
replace droplater=1 if ticker=="BI." /* BELL CANADA INTL INC, surviving ticker: BCICF */
replace droplater=1 if ticker=="BCE1." /* BELL CANADA, surviving ticker: BCE1 */
replace droplater=1 if ticker=="BGO." /* BEMA GOLD CORP, surviving ticker: BGO */
replace droplater=1 if ticker=="BEL" /* BENNETT ENVIRONMENTAL INC, surviving ticker: BEV. */
replace droplater=1 if ticker=="BR.U" /* BIG ROCK BREWERY INCOME TR, surviving ticker: BRBMF */
replace droplater=1 if ticker=="7520B" /* BIOJECT MEDICAL TECHNOL, surviving ticker: BJCT */
replace droplater=1 if ticker=="BMD." /* BIRCH MOUNTAIN RESOURCES LTD, surviving ticker: BMD */
replace droplater=1 if ticker=="BVF." /* BIOVAIL CORP, surviving ticker: BVF */
replace droplater=1 if ticker=="BRA" /* BIOMIRA INC, surviving ticker: BIOM */
replace droplater=1 if ticker=="BMM.U" /* BLACK MOUNTAIN CAPITAL CORP, surviving ticker: 3BMMUF */
replace droplater=1 if ticker=="BOWFF" /* BOARDWALK REAL ESTATE TRUST, survivin ticker: BEI.U */
replace droplater=1 if ticker=="BOW1" /* BOWATER PULP & PAPER, surviving ricker: BOW1. */
replace droplater=1 if ticker=="BZO" /* BRAZAURO RESOURCES CORP, surviving ticker: BZOFF */
replace droplater=1 if ticker=="BWR." /* BREAKWATER RESOURCES LTD, surviving ticker: 3BWLRF */
replace droplater=1 if ticker=="GGG.Z" /* BRONX VENTURES INC, surviving ticker: 3BRXVF */
replace droplater=1 if ticker=="BAM.LV.A" /* BROOKFIELD ASSET MANAGEMENT, surviving ticker: BAM */
replace droplater=1 if ticker=="BPO" /* BROOKFIELD PROPERTIES CORP, surviving ticker: BPO. */
replace droplater=1 if ticker=="CGT" /* CAE INC, surviving ticker: CAE. */
replace droplater=1 if ticker=="CFK" /* CE FRANKLIN LTD, surviving ticker: CFT. */
replace droplater=1 if ticker=="FLY.SV.A" /* CHC HELICOPTER CORP  -CL A, surviving ticker: FLI */
replace droplater=1 if ticker=="HCH." /* MERRILL LYNCH CANADA CP HLDR, surviving ticker: HCH */
replace droplater=1 if ticker=="CBJ." /* CAMBIOR INC, surviving ticker: CBJ */
replace droplater=1 if ticker=="TTV." /* CABLETEL COMMUNICATIONS CORP, surviving ticker: CCMTF */
replace droplater=1 if ticker=="CCO." /* CAMECO CORP, surviving ticker: CCJ */
replace droplater=1 if ticker=="CCH." /* CAMPBELL RESOURCES INC, surviving ticker: 3CBLRF */
replace droplater=1 if ticker=="CL." /* CANADA LIFE FINANCIAL CORP, surviving ticker: CLU */
replace droplater=1 if ticker=="CSW" /* CANADA SOUTHERN PETROLEUM, surviving ticker: CSPLF */
replace droplater=1 if ticker=="CNR." /* CANADIAN NATIONAL RAILWAY CO, surviving ticker: CNI */
replace droplater=1 if ticker=="CNQ" /* CANADIAN NATURAL RESOURCES, surviving ticker: CNQ. */
replace droplater=1 if ticker=="CP." /* CANADIAN PACIFIC RAILWAY LTD, surviving ticker: CP */
replace droplater=1 if ticker=="SNG." /* CANADIAN SUPERIOR ENERGY INC, surviving ticker: SNG */
replace droplater=1 if ticker=="CVV." /* CANALASKA VENTURES LTD, surviving ticker: 3CVVLF */
replace droplater=1 if ticker=="CCM." /* CANARC RESOURCE CORP, surviving ticker: 3CRCUF */
replace droplater=1 if ticker=="CGS.NV" /* CANWEST GLOBAL COM  -NVTG, surviving ticker: CWG */
replace droplater=1 if ticker=="CDU" /* CARDERO RESOURCE CORP, surviving ticker: CDY */
replace droplater=1 if ticker=="CRME" /* CARDIOME PHARMA CORP, surviving ticker: COM */
replace droplater=1 if ticker=="CLS.SV" /* CELESTICA INC, surviving ticker: CLS */
replace droplater=1 if ticker=="CEF.NV.A" /* CENTRAL FUND CANADA  -CL A, surviving ticker: CEF */
replace droplater=1 if ticker=="3250B" /* CENTRAL MINERA CORP, surviving ticker: 3CENMF */
replace droplater=1 if ticker=="CERTF" /* CERTICOM CORP, surviving ticker: CIC. */
replace droplater=1 if ticker=="CC." /* CHAI NA TA CORP, surviving ticker: 3CCCFF */
replace droplater=1 if ticker=="CRW." /* CINRAM INTERNATIONAL INC, surviving ticker: CNRMF */
replace droplater=1 if ticker=="CTM." /* CINTECH SOLUTIONS INC, surviving ticker: 3CMGFF */
replace droplater=1 if ticker=="CRJ" /* CLAUDE RESOURCES INC, surviving ticker: CGR */
replace droplater=1 if ticker=="CLV." /* CLEARLY CDN BEVERAGE CP, surviving ticker: 3CCBEF */
replace droplater=1 if ticker=="CDM" /* COEUR D'ALENE MINES CORP, surviving ticker: CDE */
replace droplater=1 if ticker=="CSN." /* COGNOS INC, surviving ticker: COGN */
replace droplater=1 if ticker=="3ZCCCF" /* COMMERCIAL CONSOLIDATORS CP, surviving ticker: CCZ.1 */
replace droplater=1 if ticker=="CMT." /* COMPTON PETROLEUM CORP, surviving ticker: CMZ */
replace droplater=1 if ticker=="CNY." /* CONIAGAS RESOURCES LTD, surviving ticker: 3COIGE */
replace droplater=1 if ticker=="CMC." /* CONSOLIDATED MERCANTILE INC, surviving ticker: CSLMF */
replace droplater=1 if ticker=="3CGREF" /* CORAL GOLD RESOURCES LTD, surviving ticker: CGR. */
replace droplater=1 if ticker=="CJR.NV.B" /* CORUS ENTERTAINMENT INC, surviving ticker: CJR */
replace droplater=1 if ticker=="BCB" /* COTT CORP QUE, surviving ticker: COT */
replace droplater=1 if ticker=="CXS." /* COUNSEL CORP, surviving ticker: CXSNF */
replace droplater=1 if ticker=="CRY." /* CRYPTOLOGIC INC, surviving ticker: CRYP */
replace droplater=1 if ticker=="KRY." /* CRYSTALLEX INTERNATIONAL CP, surviving ticker: KRY */
replace droplater=1 if ticker=="CLG" /* CUMBERLAND RESOURCES LTD, surviving ticker: CLG. */
replace droplater=1 if ticker=="0604B" /* CURRENT TECHNOLOGY CORP, surviving ticker: 3CRTCF */
replace droplater=1 if ticker=="CQC" /* CUSAC GOLD MINES LTD, surviving ticker: 3CUSIF */
replace droplater=1 if ticker=="2158B" /* DALECO RESOURCES CORP, surviving ticker: 3DLOV */
replace droplater=1 if ticker=="DTV." /* DATAWAVE SYSTEMS INC, surviving ticker: 3DWVS */
replace droplater=1 if ticker=="DMC." /* DATAMIRROR CORP, surviving ticker: DMCX */
replace droplater=1 if ticker=="DTGLF" /* DATEC GROUP LTD, surviving ticker: DGL. */
replace droplater=1 if ticker=="DTL" /* DECTRON INTERNATIONALE INC, surviving ticker: DECT */
replace droplater=1 if ticker=="DNR." /* DENBURY RESOURCES INC, surviving ticker: DNR */
replace droplater=1 if ticker=="DSG." /* DESCARTES SYSTEMS GROUP INC, surviving ticker: DSGX */
replace droplater=1 if ticker=="DSM." /* DESERT SUN MINING CORP, surviving ticker: DEZ */
replace droplater=1 if ticker=="8608B" /* DIAMANT ART CORP, surviving ticker: 3DIAAF */
replace droplater=1 if ticker=="9142B" /* DIVERSINET CORP, surviving ticker: 3DVNTF */
replace droplater=1 if ticker=="DTC." /* DOMTAR INC, surviving ticker: DTC */
replace droplater=1 if ticker=="DII.SV" /* DOREL INDUSTRIES INC, surviving ticker: DIIB */
replace droplater=1 if ticker=="DAX" /* DRAXIS HEALTH INC, surviving ticker: DRAX */
replace droplater=1 if ticker=="3248B" /* DUSA PHARMACEUTICALS INC, surviving ticker: DUSA */
replace droplater=1 if ticker=="DOL." /* DYNAMIC OIL & GAS INC, surviving ticker: DYOLF */
replace droplater=1 if ticker=="9624B" /* DYNAMOTIVE ENERGY SYS CORP, surviving ticker: 3DYMTF */
replace droplater=1 if ticker=="9198B" /* E DIGITAL CORP, surviving ticker: 3EDIG */
replace droplater=1 if ticker=="EGC" /* EG CAPITAL INC, surviving ticker: EGCFF */
replace droplater=1 if ticker=="EGO" /* ELDORADO GOLD CORP, surviving ticker: ELD */
replace droplater=1 if ticker=="8332B" /* ELEPHANT & CASTLE GROUP INC, surviving ticker: 3PUBSF */
replace droplater=1 if ticker=="ECA." /* ENCANA CORP, surviving ticker: ECA */
replace droplater=1 if ticker=="ENB." /* ENBRIDGE INC, surviving ticker: ENB */
replace droplater=1 if ticker=="ERF.U" /* ENERPLUS RESOURCES FUND, surviving ticker: ERF */
replace droplater=1 if ticker=="ENT.U" /* ENTERRA ENERGY TRUST, surviving ticker: EENC */
replace droplater=1 if ticker=="ETG." /* ENTREE GOLD INC, surviving ticker: EGI */
replace droplater=1 if ticker=="ECG" /* ENVOY COMMUNICATIONS GRP INC, surviving ticker: ECGI */
replace droplater=1 if ticker=="EEE.U" /* ESPRIT ENERGY TRUST, surviving ticker: EEEBF */
replace droplater=1 if ticker=="EZM" /* EUROZINC MINING CORP, surviving ticker: EZM. */
replace droplater=1 if ticker=="EXF.SV" /* EXFO ELECTRO OPTICAL ENGR, surviving ticker: EXFO */
replace droplater=1 if ticker=="EXE.SV" /* EXTENDICARE INC  -SVTG, surviving ticker: EXE */
replace droplater=1 if ticker=="TPC.Z" /* FEC RESOURCES INC, surviving ticker: 3FECOF */
replace droplater=1 if ticker=="FNX." /* FNX MINING CO INC, surviving ticker: FNX */
replace droplater=1 if ticker=="FFH" /* FAIRFAX FINANCIAL HOLDINGS, surviving ticker: FFH.SV */
replace droplater=1 if ticker=="FHR." /* FAIRMONT HOTELS RESORTS INC, surviving ticker: FHR */
replace droplater=1 if ticker=="FAL.LV" /* FALCONBRIDGE LTD, surviving ticker: FAL */
replace droplater=1 if ticker=="FTM" /* FANTOM TECHNOLOGIES INC, surviving ticker: FTMTQ */
replace droplater=1 if ticker=="FSV.SV" /* FIRSTSERVICE CORP  -SVTG, surviving ticker: FSRV */
replace droplater=1 if ticker=="FMI." /* FORBES MEDI-TECH INC, surviving ticker: FMTI */
replace droplater=1 if ticker=="FDG.U" /* FORDING CANADIAN COAL TRUST, surviving ticker: FDG */
replace droplater=1 if ticker=="FSH.SV" /* FOUR SEASONS HOTELS -LTD VTG, surviving ticker: FS */
replace droplater=1 if ticker=="FRG." /* FRONTEER DEVELOPMENT GRP INC, surviving ticker: FRG */
replace droplater=1 if ticker=="GSI." /* GSI GROUP INC, surviving ticker: GSIG */
replace droplater=1 if ticker=="GRS" /* GAMMON LAKE RESOURCES INC, surviving ticker: GAM. */
replace droplater=1 if ticker=="YGA" /* GASTAR EXPLORATION LTD, surviving ticker: GST */
replace droplater=1 if ticker=="GAC." /* GEAC COMPUTER CORP LTD, surviving ticker: GEAC */
replace droplater=1 if ticker=="GNA." /* GERDAU AMERISTEEL CORP, surviving ticker: GNA */
replace droplater=1 if ticker=="GIL." /* GILDAN ACTIVEWEAR INC, surviving ticker: GIL */
replace droplater=1 if ticker=="GLG." /* GLAMIS GOLD LTD, surviving ticker: GLG. */
replace droplater=1 if ticker=="GGG." /* GLENCAIRN GOLD CORP, surviving ticker: GLE */
replace droplater=1 if ticker=="GGB.Z" /* GLOBAL LT TELECOMMUNICATIONS, ticker: GBTI */
replace droplater=1 if ticker=="CLP." /* GLOBETECH VENTURES CORP, surviving ticker: 3GTVCF */
replace droplater=1 if ticker=="G." /* GOLDCORP INC, surviving ticker: GG */
replace droplater=1 if ticker=="GGR." /* GOLDEN GOOSE RESOURCE, surviving ticker: 3GGOSF */
replace droplater=1 if ticker=="GSC." /* GOLDEN STAR RESOURCES LTD, surviving ticker: GSS */
replace droplater=1 if ticker=="GNC." /* GORAN CAPITAL INC, surviving ticker: GNCN */
replace droplater=1 if ticker=="7637B" /* GRANDETEL TECHNOLOGIES INC, surviving ticker: GTTIF */
replace droplater=1 if ticker=="GBG" /* GREAT BASIN GOLD LTD, surviving ticker: GBN */
replace droplater=1 if ticker=="GIB" /* CGI GROUP INC  -CL A, surviving ticker: GIB.SV.A */
replace droplater=1 if ticker=="HTE.U" /* HARVEST ENERGY TRUST, surviving ticker: HTE */
replace droplater=1 if ticker=="3269B" /* HEALTHTRAC INC, surviving ticker: HTAC */
replace droplater=1 if ticker=="HMSLQ" /* HEMOSOL CORP, surviving ticker: HML. */
replace droplater=1 if ticker=="HLG.C" /*HOLLINGER INC, surviving ticker: HLGAF */
replace droplater=1 if ticker=="HBG" /* HUB INTERNATIONAL LTD, surviving ticker: HBG. */
replace droplater=1 if ticker=="HUM." /* HUMMINGBIRD LTD, surviving ticker: HUMC */
replace droplater=1 if ticker=="HYG" /* HYDROGENICS CORP, surviving ticker: HYGA */
replace droplater=1 if ticker=="IMR" /* IMA EXPLORATION INC, surviving ticker: IMR. */
replace droplater=1 if ticker=="IMG." /* IAMGOLD CORP, surviving ticker: IAG */
replace droplater=1 if ticker=="IMX." /* IMAX CORP, surviving ticker: IMAX */
replace droplater=1 if ticker=="IGP" /* IMPERIAL GINSENG PRODS LTD, surviving ticker: IGPFF */
replace droplater=1 if ticker=="IMO." /* IMPERIAL OIL LTD, surviving ticker: IMO */
replace droplater=1 if ticker=="N." /* INCO LTD, surviving ticker: N */
replace droplater=1 if ticker=="INP" /* INFOCORP COMPUTER SOLUTIONS, surviving ticker: IFCSF */
replace droplater=1 if ticker=="INO" /* INOVIO BIOMEDICAL CORP, surviving ticker: GEB. */
replace droplater=1 if ticker=="3IBTGF" /* INTERNATIONAL BARRIER TECH I, surviving ticker: IBH */
replace droplater=1 if ticker=="7116B" /* INTL GEMINI TECH INC, surviving ticker: 6971B */
replace droplater=1 if ticker=="ITW." /* INTRAWEST CORP, surviving ticker: IDR */
replace droplater=1 if ticker=="ITP." /* INTERTAPE POLYMER GROUP INC, surviving ticker: ITP */
replace droplater=1 if ticker=="IOL" /* INTEROIL CORP, surviving ticker: IOC */
replace droplater=1 if ticker=="IPS." /* IPSCO INC, surviving ticker: IPS */
replace droplater=1 if ticker=="ITX" /* ITERATION ENERGY LTD, surviving ticker: ITXFF */
replace droplater=1 if ticker=="IE" /* IVANHOE ENERGY INC, surviving ticker: IVAN */
replace droplater=1 if ticker=="IVN" /* IVANHOE MINES LTD, surviving ticker: IVN. */
replace droplater=1 if ticker=="JCT." /* JEWETT-CAMERON TRADING CO, surviving ticker: JCTCF */
replace droplater=1 if ticker=="JSD" /* JONES SODA CO, surviving ticker: JSDA */
replace droplater=1 if ticker=="MXB.U" /* KHD HUMBOLDT WEDAG INTL LTD, surviving ticker: KHDH */
replace droplater=1 if ticker=="KBR" /* KIMBER RESOURCES INC surviving ticker: KBX */
replace droplater=1 if ticker=="K." /* KINROSS GOLD CORP, surviving ticker: KGC */
replace droplater=1 if ticker=="KFS" /* KINGSWAY FINANCIAL SVCS INC, surviving ticker: KFS. */
replace droplater=1 if ticker=="LMZ." /* LMS MEDICAL SYSTEMS INC, surviving ticker: LMZ */
replace droplater=1 if ticker=="KRK" /* LML PAYMENT SYSTEMS INC, surviving ticker: LMLP */
replace droplater=1 if ticker=="BUS." /* LAIDLAW INTERNATIONAL INC, surviving ticker: LI */
replace droplater=1 if ticker=="LAG" /* LAURION GOLD INC, surviving ticker: LAGFF */
replace droplater=1 if ticker=="9008B" /* LEADING BRANDS INC, surviving ticker: LBIX */
replace droplater=1 if ticker=="LVN" /* LEVON RESOURCES LTD, surviving ticker: 3LVNVE */
replace droplater=1 if ticker=="LIM" /* LIONORE MINING INTL LTD, surviving ticker: 3LMGGF */
replace droplater=1 if ticker=="LGF." /* LIONS GATE ENTMT CORP, surviving ticker: LGF */
replace droplater=1 if ticker=="LQW" /* LIQUIDATION WORLD INC, surviving ticker: LIQWF */
replace droplater=1 if ticker=="LOR." /* LORUS THERAPEUTICS INC, surviving ticker: LRP */
replace droplater=1 if ticker=="MDZ.SV.A" /* MDC PARTNERS INC, surviving ticker: MDCA */
replace droplater=1 if ticker=="MDZ" /* MDS INC, surviving ticker: MDS. */
replace droplater=1 if ticker=="MIM.SV.A" /* MI DEVELOPMENTS INC, surviving ticker: MIM */
replace droplater=1 if ticker=="MCZ." /* MAD CATZ INTERACTIVE INC, surviving ticker: MCZ */
replace droplater=1 if ticker=="3MMRSF" /* MADISON MINERALS INC, surviving ticker: MMR. */
replace droplater=1 if ticker=="MEC.SV.A" /* MAGNA ENTERTAINMENT CORP, surviving ticker: MECA */
replace droplater=1 if ticker=="MG.SV.A" /* MAGNA INTERNATIONAL  -CL A, surviving ticker: MGA */
replace droplater=1 if ticker=="8627B" /* MAMMA.COM INC, surviving ticker: MAMA */
replace droplater=1 if ticker=="MFC." /* MANULIFE FINANCIAL CORP, surviving ticker: MFC */
replace droplater=1 if ticker=="3393B" /* MED-EMERG INTERNATIONAL INC, surviving ticker: 3MDER */
replace droplater=1 if ticker=="MPH." /* MEDICURE INC, surviving ticker: MCU */
replace droplater=1 if ticker=="MNG." /* MERIDIAN GOLD INC, surviving ticker: MDG */
replace droplater=1 if ticker=="MR." /* METALLICA RESOURCES INC, surviving ticker: MRB */
replace droplater=1 if ticker=="MX." /* METHANEX CORP, surviving ticker: MEOH */
replace droplater=1 if ticker=="0455B" /* MICROMEM TECHNOLOGIES INC, surviving ticker: 3MMTIF */
replace droplater=1 if ticker=="MGI." /* MIGENIX INC, surviving ticker: 3MGIFF */
replace droplater=1 if ticker=="MMM." /* MINCO MINING & METALS CORP, surviving ticker: MMK */
replace droplater=1 if ticker=="MFN" /* MINEFINDERS CORP LTD, surviving ticker: MFL. */
replace droplater=1 if ticker=="MAE" /* MIRAMAR MINING CORP, surviving ticker: MNG */
replace droplater=1 if ticker=="MNT." /* MIRTRONICS INC, surviving ticker: 3MNTCE */
replace droplater=1 if ticker=="MPV." /* MOUNTAIN PROVINCE DIAMONDS, surviving ticker: MDM */
replace droplater=1 if ticker=="NBS." /* NBS TECHNOLOGIES INC, surviving ticker: NBSFF */
replace droplater=1 if ticker=="7518B" /* NGAS RESOURCES INC, surviving ticker: NGAS */
replace droplater=1 if ticker=="NQL." /* NQL ENERGY SERVICES INC, surviving ticker: 3NQDRF */
replace droplater=1 if ticker=="NRM" /* NEUROCHEM INC, surviving ticker: NRMX */
replace droplater=1 if ticker=="NEV." /* NEVADA STAR RESOURCE CORP, surviving ticker: 3NVSRF */
replace droplater=1 if ticker=="NSU" /* NEVSUN RESOURCES LTD, surviving ticker: NSU. */
replace droplater=1 if ticker=="NGD." /* NEW GOLD INC, surviving ticker: NGD */
replace droplater=1 if ticker=="NXY." /* NEXEN INC, surviving ticker: NXY */
replace droplater=1 if ticker=="3636B" /* NICHOLAS FINANCIAL INC, surviving ticker: NICK */
replace droplater=1 if ticker=="NBD." /* NORBORD INC, surviving ticker: NBDFF */
replace droplater=1 if ticker=="NII." /* NORSAT INTL INC, surviving ticker: 3NSATF */
replace droplater=1 if ticker=="NT." /* NORTEL NETWORKS CORP, surviving ticker: NT */
replace droplater=1 if ticker=="PDL" /* NORTH AMERICAN PALLADIUM, surviving ticker: PAL */
replace droplater=1 if ticker=="NDM" /* NORTHERN DYNASTY MINERAL LTD, surviving ticker: NAK */
replace droplater=1 if ticker=="NHD" /* NORTHERN HEMISPHERE DEV CP, surviving ticker: 3HSDME */
replace droplater=1 if ticker=="NNO." /* NORTHERN ORION RESOURCES INC, surviving ticker: NTO */
replace droplater=1 if ticker=="NGX." /* NORTHGATE MINERALS CORP, surviving ticker: NGX */
replace droplater=1 if ticker=="NCX." /* NOVA CHEMICALS CORP, surviving ticker: NCX */
replace droplater=1 if ticker=="NG." /* NOVAGOLD RESOURCES LTD, surviving ticker: NG */
replace droplater=1 if ticker=="3311B" /* NOVAMERICAN STEEL INC, surviving ticker: TONS */
replace droplater=1 if ticker=="NVL." /* NOVELIS INC, surviving ticker: NVL */
replace droplater=1 if ticker=="NCS." /* NUCRYST PHARM CORP -REDH, surviving ticker: NCST */
replace droplater=1 if ticker=="0115B" /* NYMOX PHARMACEUTICAL CORP, surviving ticker: NYMX */
replace droplater=1 if ticker=="3OFSYF" /* OFFSHORE SYSTEMS INTL LTD, surviving ticker: OSI. */
replace droplater=1 if ticker=="ONC" /* ONCOLYTICS BIOTECH INC, surviving ticker: ONCY */
replace droplater=1 if ticker=="OTC" /* OPEN TEXT CORP, surviving ticker: OTEX */
replace droplater=1 if ticker=="OPY.NV" /* OPPENHEIMER HLDGS INC  -CL A, surviving ticker: OPY */
replace droplater=1 if ticker=="2129B" /* OPTIMAL GROUP INC, surviving ticker: 2129B */
replace droplater=1 if ticker=="OZN." /* OREZONE RESOURCES INC, surviving ticker: OZN */
replace droplater=1 if ticker=="OTT.U" /* OTELCO INC, suviving ticker: OTT */
replace droplater=1 if ticker=="7598B" /* PLC SYSTEMS INC, surviving ticker: PLC */
replace droplater=1 if ticker=="PMU." /* PACIFIC RIM MINING CORP, surviving ticker: PMU */
replace droplater=1 if ticker=="PAA." /* PAN AMERICAN SILVER CORP, surviving ticker: PAAS */
replace droplater=1 if ticker=="PEI." /* PARLAY ENTERTAINMENT INC, surviving ticker: PRYNF */
replace droplater=1 if ticker=="PAE.LV"  /* PEACE ARCH ENTMT GROUP INC, surviving ticker: PAE */ 
replace droplater=1 if ticker=="PGF.A" /* PENGROWTH ENERGY TRUST, surviving ticker: PGH */
replace droplater=1 if ticker=="PCR." /* PERU COPPER INC, surviving ticker: CUP */
replace droplater=1 if ticker=="PTQ" /* PETAQUILLA MINERALS LTD, surviving ticker: 3PTQMF */
replace droplater=1 if ticker=="PCA." /* PETRO-CANADA, surviving ticker: PCZ */
replace droplater=1 if ticker=="PTF.U" /* PETROFUND ENERGY TRUST, surviving ticker: PFT */
replace droplater=1 if ticker=="PQU" /* PETROQUEST ENERGY INC, suviving ticker: PQ */
replace droplater=1 if ticker=="PDG." /* PLACER DOME INC, surviving ticker: PDG */
replace droplater=1 if ticker=="LAN." /* PLAINTREE SYSTEMS INC, surviving ticker: 3LANPF */
replace droplater=1 if ticker=="PPK." /* POLYAIR INTER PACK INC, surviving ticker: PPK */
replace droplater=1 if ticker=="PYM." /* POLYMER SOLUTIONS INC, surviving ticker: 3PYSU */
replace droplater=1 if ticker=="POM." /* POLYMET MINING CORP, surviving ticker: 3POMGF */
replace droplater=1 if ticker=="POT." /* POTASH CORP SASK INC, surviving ticker: POT */
replace droplater=1 if ticker=="PRP" /* PRAIRIE PACIFIC ENERGY CORP, surviving ticker: 3PRPEF */
replace droplater=1 if ticker=="PD.U" /* PRECISION DRILLING TRUST, surviving ticker: PDS */
replace droplater=1 if ticker=="PMD." /* PREMD INC, surviving ticker: PME */
replace droplater=1 if ticker=="PBI.U" /* PREMIUM BRANDS INCOME FUND, surviving ticker: PRMBF */
replace droplater=1 if ticker=="PWI.U" /* PRIMEWEST ENERGY TRUST, surviving ticker: PWI */
replace droplater=1 if ticker=="7050B" /* PRINCETON MEDIA GROUP INC, surviving ticker: PMGIF */
replace droplater=1 if ticker=="PVE.U" /* PROVIDENT ENERGY TRUST, surviving ticker: PVX */
replace droplater=1 if ticker=="QLT" /* QLT INC, surviving ticker: GLTI */
replace droplater=1 if ticker=="AAZ" /* QSOUND LABS INC, surviving ticker: QSND */
replace droplater=1 if ticker=="QZM" /* QUARTZ MOUNTAIN RESOURCES, surviving ticker: 3QZMRE */
replace droplater=1 if ticker=="QBR.SV.B" /* QUEBECOR INC  -CL B, surviving ticker: 3QBCRF */
replace droplater=1 if ticker=="IQW.SV" /* QUEBECOR WORLD INC  -SUB VTG, surviving ticker: IQW */
replace droplater=1 if ticker=="QRL" /* QUEENSTAKE RES LTD YUKON, surviving ticker: QEE */
replace droplater=1 if ticker=="QC" /* QUEST CAPITAL CORP, surviving ticker: QCC */
replace droplater=1 if ticker=="GHVRF" /* RED LAKE RESOURCES INC, surviving ticker: RL. */
replace droplater=1 if ticker=="RLO." /* REGALITO COPPER CORP, surviving ticker: RLO */
replace droplater=1 if ticker=="RGF." /* REPUBLIC GOLDFIELDS, surviving ticker: 3RPGDF */
replace droplater=1 if ticker=="RIM" /* RESEARCH IN MOTION LTD, surviving ticker: RIMM */
replace droplater=1 if ticker=="RPC." /* REVENUE PROPERTIES CO LTD, surviving ticker: RPCFF */
replace droplater=1 if ticker=="RIC." /* RICHMONT MINES INC, surviving ticker: RIC */
replace droplater=1 if ticker=="RNO" /* RIO NARCEA GOLD MINES LTD, surviving ticker: RNG */
replace droplater=1 if ticker=="RBA." /* RITCHIE BROS AUCTIONEERS INC, surviving ticker: RBS */
replace droplater=1 if ticker=="RTI." /* RIVAL TECHNOLOGIES INC, surviving ticker: 3RVTI */
replace droplater=1 if ticker=="RE." /* ROCK ENERGY INC, surviving ticker: RENFF */
replace droplater=1 if ticker=="RCI.NV.B" /* ROGERS COMMUNICATIONS  -CL B, surviving ticker: RG */
replace droplater=1 if ticker=="3591B" /* ROSEDALE DECORATIVE PROD LTD, surviving ticker: ROSD */
replace droplater=1 if ticker=="RYG." /* ROYAL GROUP TECHNOLOGIES LTD, surviving ticker: RYG */
replace droplater=1 if ticker=="ROY.U" /* ROYSTER-CLARK LTD, surviving ticker: 0912B */
replace droplater=1 if ticker=="RMX." /* RUBICON MINERALS CORP, surviving ticker: RBY */
replace droplater=1 if ticker=="RSM" /* ROYAL STANDARD MINERALS INC, surviving ticker: 3RYSMF */
replace droplater=1 if ticker=="RUS." /* RUSSEL METALS INC, surviving ticker: RUSMF */
replace droplater=1 if ticker=="SRXAF" /* S R TELECOM INC, surviving ticker: SRX. */
replace droplater=1 if ticker=="9022B" /* SCHMITT INDUSTRIES INC/OR, surviving ticker: SMIT */
replace droplater=1 if ticker=="SLP." /* SCINTILORE EXPLORATIONS LTD, surviving ticker: SLPFF */
replace droplater=1 if ticker=="SEA." /* SEABRIDGE GOLD INC, surviving ticker: SA */
replace droplater=1 if ticker=="3SNSG" /* 3SNSGSENSE TECHNOLOGIES INC, surviving ticker: SSE.Z */
replace droplater=1 if ticker=="SVN" /* 724 SOLUTIONS INC, surviving ticker: SVNX */
replace droplater=1 if ticker=="SJR" /* SHAW COMMUNICATIONS INC-CL B, surviving ticker: SJR.NV.B */
replace droplater=1 if ticker=="SHC." /* SHELL CANADA LTD  -CL A, surviving ticker: 3SCUAF */
replace droplater=1 if ticker=="0416B" /* SHEP TECHNOLOGIES INC, surviving ticker: STLOF */
replace droplater=1 if ticker=="SW." /* SIERRA WIRELESS INC, surviving ticker: SWIR */
replace droplater=1 if ticker=="SWE" /* SILENT WITNESS ENTERPRISES, surviving ticker: SILWF */
replace droplater=1 if ticker=="8710B" /* SILVERADO GOLD MINES LTD, surviving ticker: 3SLGLF */
replace droplater=1 if ticker=="SSO" /* SILVER STANDARD RES INC, surviving ticker: SSRI */
replace droplater=1 if ticker=="SLW" /* SILVER WHEATON CORP, surviving ticker: SLW. */
replace droplater=1 if ticker=="SOLI" /* SOLITARIO RESOURCES CORP, surviving ticker: SLR. */
replace droplater=1 if ticker=="SDI" /* SPECTRAL DIAGNOSTICS INC, surviving ticker: DIAGF */
replace droplater=1 if ticker=="SSY." /* SPECTRUM SIGNAL PROCESSING, surviving ticker: SSPI */
replace droplater=1 if ticker=="SXC" /* STANTEC INC, surviving ticker: STN. */
replace droplater=1 if ticker=="SLX" /* STELLAR PHARMACEUTICALS INC, surviving ticker: 3SLXCF */
replace droplater=1 if ticker=="SGD" /* STERLITE GOLD LTD, surviving ticker: SGDTF */
replace droplater=1 if ticker=="SME" /* STORM CAT ENERGY CORP, surviving ticker: SCU */
replace droplater=1 if ticker=="SLF." /* SUN LIFE FINANCIAL INC, surviving ticker: SLF */
replace droplater=1 if ticker=="SU." /* SUNCOR ENERGY INC, surviving ticker: SU */
replace droplater=1 if ticker=="SOY" /* SUNOPTA INC, surviving ticker: STKL */
replace droplater=1 if ticker=="TLC" /* TLC VISION CORP, surviving ticker: TLSV */
replace droplater=1 if ticker=="TLM." /* TALISMAN ENERGY INC, surviving ticker: TLM */
replace droplater=1 if ticker=="TNX" /* TAN RANGE EXPLORATION CP, surviving ticker: TRE */
replace droplater=1 if ticker=="TKO." /* TASEKO MINES LTD, surviving ticker: TGB */
replace droplater=1 if ticker=="TNC." /* TELESIS NORTH COMMUNICATIONS, surviving ticker: TNCVF */
replace droplater=1 if ticker=="TU" /* TELUS CORP, surviving ticker: T.NV */
replace droplater=1 if ticker=="TIW." /* TELESYSTEM INTL WIRELESS INC, surviving ticker: TIWIF */
replace droplater=1 if ticker=="TEO." /* TESCO CORP, surviving ticker: TESOF */
replace droplater=1 if ticker=="TOC." /* THOMSON CORP, surviving ticker: TOC */
replace droplater=1 if ticker=="0458B" /* TRANS ORIENT PETROLEUM LTD, surviving ticker: 3TOPLF */
replace droplater=1 if ticker=="TAC" /* TRANSALTA CORP, surviving ticker: TA: */
replace droplater=1 if ticker=="TRP." /* TRANSCANADA CORP, surviving ticker: TRP */
replace droplater=1 if ticker=="TGL." /* TRANSGLOBE ENERGY CORP, surviving ticker: TGA */
replace droplater=1 if ticker=="TRZ.1" /* TRIZEC PROPERTIES INC, surviving ticker: TRZ */
replace droplater=1 if ticker=="TDR." /* TUDOR CORP LTD, surviving ticker: TDRLF */
replace droplater=1 if ticker=="UP" /* ULTRA PETROLEUM CORP, surviving ticker: UPL */
replace droplater=1 if ticker=="UVI" /* UNILENS VISION INC, surviving ticker: 3UVICF */
replace droplater=1 if ticker=="VAS." /* VASOGEN INC, surviving ticker: VSGN */
replace droplater=1 if ticker=="VIR." /* VIREXX MEDICAL CORP, surviving ticker: REX */
replace droplater=1 if ticker=="VGZ." /* VIREXX MEDICAL CORPVISTA GOLD CORP, surviving ticker: VGZ */
replace droplater=1 if ticker=="3VISRF" /* VISIPHOR CORP, surviving ticker: VIS. */
replace droplater=1 if ticker=="VTN." /* VITRAN CORP INC, surviving ticker: VTNC */
replace droplater=1 if ticker=="WCS.SV.A" /* WESCAST INDUSTRIES  -CL A, surviving ticker: WCSTF */
replace droplater=1 if ticker=="WED" /* WESTAIM CORP, surviving ticker: WEDX */
replace droplater=1 if ticker=="WTZ" /* WESTERN SILVER CORP, surviving ticker: WTC */
replace droplater=1 if ticker=="9354B" /* WIDECOM GROUP INC, surviving ticker: WIDE */
replace droplater=1 if ticker=="WHT." /* WORLD HEART CORP, surviving ticker: WHRT */
replace droplater=1 if ticker=="YM" /* YM BIOSCIENCES INC, surviving ticker: YMI */
replace droplater=1 if ticker=="YRI" /* YAMANA GOLD INC, surviving ticker: AUY */
replace droplater=1 if ticker=="ZIC" /* ZI CORP, surviving ticker: ZICA */
replace droplater=1 if ticker=="ZL." /* ZARLINK SEMICONDUCTOR INC, surviving ticker: ZL */
replace droplater=1 if ticker=="8115B" /* ZOOM TECHNOLOGIES INC, surviving ticker: ZOOM */
replace droplater=1 if ticker=="ZTE" /* ZTEST ELECTRONICS INC, surviving ticker: ZTSTF */
replace droplater=1 if ticker=="POC.SV.B" /* PAN-OCEAN ENERGY CORP -CL B, surviving ticker: PNYBF */
replace droplater=1 if ticker=="SVS.U" /* SEVEN SEAS PETROLEUM INC, surviving ticker: 3SVSSF */

*additional drops, which became nessessary, caused by the merge of comp and segm.
* surviving ticker are in the segment data set.

replace droplater=1 if ticker=="AFM.1" /* AMERICAN RADIO SYS CP  -CL A, surviving ticker: AFM.2 */
replace droplater=1 if ticker=="AREN" /* ARGOSY ENERGY INC, surviving ticker: AREN. */
replace droplater=1 if ticker=="BOSS" /* BUILDING ONE SVCS CORP, surviving ticker: BOSS.1 */
replace droplater=1 if ticker=="3CCSE" /*COMMUNITY CARE SVCS INC, 3CCSE. */
replace droplater=1 if ticker=="CMPS" /*COMPASS INTL SVCS CORP,surviving ticker: CMPS.1*/
replace droplater=1 if ticker=="DLGC" /* DIALOGIC CORP ,surviving ticker: DLGC.1*/
replace droplater=1 if ticker=="3FSNR" /*FRANKLIN SELECT RLTY   -CL A,surviving ticker: 3FSNR.*/
replace droplater=1 if ticker=="SCHA" /*SMART CHOICE AUTO GRP,surviving ticker: SCHA.1*/
replace droplater=1 if ticker=="3WSDIE" /* WALL ST DELI INC ,surviving ticker: WSDI*/


drop if droplater==1
drop droplater
desc

save "clean_compustat_master.dta", replace

log close

